# -*- coding: utf-8 -*-
# @Time    : 18-6-28 下午8:40
# @Author  : hp
# @Email   : shaoeric@foxmail.com
import pymysql
from time import sleep, localtime, mktime, strftime, strptime,time
from random import randint, choice

# 打开数据库连接  配置项：连接、用户名、密码、数据库名 编码
db = pymysql.connect("localhost", "root", "root", "bookSale", charset='utf8')

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()

with open('books2.json', 'r', encoding='utf-8') as f:
    lst = eval(f.read())


def insertBookInfo():
    for item in lst:
        try:
            sql = "insert into Infor VALUES ('{}', '{}', '{}', '{}', '{}', '{}', '{}')".format(item['图书编号'], item['书名'], item['作者'], item['出版社'], 1, item['售价'], item['定价'])
            cursor.execute(sql)
            db.commit()
            sleep(0.2)
        except Exception as e:
            print(str(e))
            pass

def insertInventory():
    for item in lst:
        try:
            sql = "insert into Inventory VALUES ('{}', '{}')".format(item['图书编号'], randint(50, 100))
            cursor.execute(sql)
            db.commit()
            sleep(0.2)
        except Exception as e:
            print(str(e))
            pass

def insertBuyin():
    a1=(2017,1,1,0,0,0,0,0,0)       #设置开始日期时间元组（1976-01-01 00：00：00）
    a2=(2018,6,30,23,59,59,0,0,0)  #设置结束日期时间元组（1990-12-31 23：59：59）
    start = mktime(a1)  #生成开始时间戳
    end = mktime(a2)   #生成结束时间戳
    for item in lst:
        try:
            t = randint(start, end)
            date_touple = localtime(t)
            date = strftime("%Y-%m-%d", date_touple)
            sql = "insert into Buyin VALUES ('{}', '{}', '{}', '{}', '{}')".format(item['图书编号'], randint(1, 11), 100, item['售价'], date)
            cursor.execute(sql)
            db.commit()
            sleep(0.2)
        except Exception as e:
            print(str(e))
            pass

def insertSale():
    for item in lst:
        try:
            timequery = "select buytime from Buyin where isbn='{}'".format(item['图书编号'])
            cursor.execute(timequery)
            datetime = str(cursor.fetchone()[0]).split('-')
            year = choice(list(range(int(datetime[0]), 2019)))
            month = choice(list(range(int(datetime[1]), 13)))
            day = choice(list(range(int(datetime[2]), 32)))
            date = "{}-{}-{}".format(year, month, day)

            sql = "insert into Sale VALUES ('{}', '{}', '{}', '{}', '{}','买家{}')".format(item['图书编号'],hash(randint(1, 1000)+time())//10000000000, max(0.8*float(item['定价']),float(item['售价'])), randint(1, 5), date, randint(1, 100))
            cursor.execute(sql)
            db.commit()
            sleep(0.1)
        except Exception as e:
            print(str(e))
            pass

def main():
    insertBookInfo()
    insertBuyin()
    insertInventory()
    insertSale()
    print("finish")
    cursor.close()
    db.close()

if __name__ == '__main__':
    main()